#!/usr/bin/env python
# encoding: utf-8
"""
coveyRetention.py

Created by Brant Faircloth on 2008-01-05.
Copyright (c) 2008 Brant C. Faircloth. All rights reserved.

A script for determining birds carrying over from covey to covey in successive years
of the study.

"""

import MySQLdb, pdb

def getRecords(c,y):
    # get the birds that are retained over years
    query = (("select distinct(bird_id) from ttrs_covey_members where year = %s and bird_id in (select bird_id from ttrs_covey_members where year = %s)") % (y,y+1))
    c.execute(query)
    birds = c.fetchall()
    print (("Number of surviving covey mates:  %s") % (len(birds)))
    for bird in birds:
        # get the covey of the bird of interest
        #pdb.set_trace()
        query = (("select covey_id from ttrs_covey_members where year = %s and bird_id = '%s' and transient != 1") % (y,bird[0]))
        c.execute(query)
        covey = c.fetchall()
        # get the birds it was with in the first year
        query = (("select distinct(bird_id) from ttrs_covey_members where year = %s and covey_id = %s and transient != 1 and bird_id != %s") % (y,covey[0][0],bird[0]))
        c.execute(query)
        friends = c.fetchall()
        # get the covey it was with in the second year
        query = (("select covey_id from ttrs_covey_members where year = %s and bird_id = '%s' and transient != 1") % (y+1,bird[0]))
        c.execute(query)
        newCovey = c.fetchall()
        # see if the birds from the first year are with it in the second year
        query = (("select distinct(bird_id) from ttrs_covey_members where year = %s and covey_id = %s and transient != 1 and bird_id != %s") % (y+1,newCovey[0][0],bird[0]))
        c.execute(query)
        newFriends = c.fetchall()
        # get a list of all bird to see if any of the original covey-mates are alive but in diff. coveys
        query = (("select bird_id from ttrs_covey_members where year = %s and bird_id != %s and covey_id != %s and transient != 1") % (y+1,bird[0],newCovey[0][0]))
        c.execute(query)
        allNewBirds = c.fetchall()
        tempL1 = []
        tempL2 = []
        for f in friends:
            if f in newFriends:
                tempL1.append(f)
            if f in allNewBirds:
                tempL2.append(f)
        print (("%s Y1: %s Y2: %s Same covey: %s; Diff Coveys: %s") % (bird,y,y+1,tempL1,tempL2))


def main():
    year = [2002]
    database = MySQLdb.connect("params")
    c = database.cursor()
    for y in year:
        getRecords(c,y)
    c.close()


if __name__ == '__main__':
    main()

